package gem.database;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.Vector;

import gem.model.*;
import gem.model.course.Association;
import gem.model.course.Course;
import gem.model.course.Department;
import gem.model.course.ScienceDomain;

// glavna klasa za citanje entiteta iz baze 
public class ReaderWriter
{
	//====================================================//
	static public java.util.Vector<Department> loadDepartments()
	{
		String query = "SELECT * FROM Department";
		
		java.util.Vector<Department> departments = null;
		try
		{
			java.sql.ResultSet rs = DBConnection.getConnection().createStatement().executeQuery( query );
			departments = new Vector<Department>();
			while( rs.next())
			{
				Department d = new Department( rs.getString("DepartmentTitle"), rs.getInt("DepartmentID") );
				departments.add( d );
			}
			rs.close();
		}
		catch( SQLException e )
		{
			e.printStackTrace();
		}
		catch( Exception e )
		{
			e.printStackTrace();
		}
		finally
		{
			return departments;
		}		
	}
	//====================================================//
	//====================================================//
	static public java.util.Vector<ScienceDomain> loadScienceDomains( Department d )
	{
		// u bazi imamo vise naucnih oblasti u okviru jedne katedre...
		// jedna naucna oblast se pojavljuje onoliko puta u sciencedomaincourses tabeli
		// koliko ima i predmeta pred njoj, zato smo stavili distinct, da bismo izbegli duplikate
		String query = "SELECT DISTINCT sd.ScienceDomainTitle, sd.sciencedomainID " +
		"FROM sciencedomain as sd, sciencedomaincourses as sdc " +
		"WHERE sd.sciencedomainID = sdc.sciencedomainID AND " +
		"sdc.departmentID = " + d.getId() + ";" +
		"";
		
		java.util.Vector< ScienceDomain > sds = new Vector<ScienceDomain>();
		try
		{
			java.sql.ResultSet rs = DBConnection.getConnection().createStatement().executeQuery( query );
			
			while( rs.next())
			{
				ScienceDomain sd = new ScienceDomain(rs.getString("ScienceDomainTitle"), rs.getInt("ScienceDomainId"), d);
				//System.out.println("Added " + sd );
				sds.add(sd);
			}
			rs.close();
		}
		catch( SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			return sds;
		}
	}
	//====================================================//
	//====================================================//
	//====================================================//
	//====================================================//
	static public java.util.Vector<Course> loadCourses( ScienceDomain sd )
	{
		String query = "SELECT c.CourseTitle, c.CourseID " +
		"FROM course as c, sciencedomaincourses as sdc " +
		"WHERE c.courseID = sdc.courseID AND " +
		"sdc.sciencedomainID = " + sd.getId() + ";" +
		"";
		
		java.util.Vector<Course> cs = new Vector<Course>();
		
		try
		{
			java.sql.ResultSet rs = DBConnection.getConnection().createStatement().executeQuery( query );
			
			while( rs.next() )
			{
				Course c = new Course(rs.getString("CourseTitle"), rs.getInt("CourseId"), sd );
				
				cs.add( c );
			}
			rs.close();
		}
		catch( SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			return cs;
		}
		
	}
	//====================================================//

	//====================================================//
	public static java.util.Vector<Department> loadDepartmentsFully()
	{
		java.util.Vector<Department> deps;
		
		deps = loadDepartments();
		
		for( Department d : deps )
		{
			java.util.Vector< ScienceDomain >sDomains = loadScienceDomains( d );
			for( ScienceDomain sd : sDomains)
			{
				java.util.Vector<Course> courses = loadCourses(sd);
				
				// Set association table
				for( Course c : courses )
				{
					Association.addAssociation( d.getId(), sd.getId(), c.getId() );
				}
				
				sd.setCourses(courses);
			}
			d.setScienceDomains(sDomains);
		}
		return deps;
	}
	//====================================================//
	//====================================================//
	public static void storeDepartment( Department dep ) throws Exception
	{
		throw new Exception("Not implemented");
	}
	//====================================================//
	public static void storeDepartmentFully( Department dep )
	{
		if( dep.getId() == 0 )
		{
			insertNewDepartmentFully(dep);
		}
		// update name in table Department
		String query = "";
		// update science domains - just names
		// update courses - just names
		// update associations!!!
	}
	//====================================================//
	public static void insertNewDepartmentFully( Department d )
	{
		String insertDepQuery = "INSERT INTO Department (DepartmentTitle) VALUES ( '" + d.getTitle() + "' );";
		int depId;
		
		try
		{
			// treba nam povratna vrednost iz INSERT-a, kljuc nove katedre
			java.sql.PreparedStatement pStat = DBConnection.getConnection().prepareStatement(insertDepQuery, java.sql.Statement.RETURN_GENERATED_KEYS);
			int affectedRows = pStat.executeUpdate();
			if( affectedRows == 0 )
			{
				throw new SQLException("Kreiranje katedre nije izvrseno");
			}
			java.sql.ResultSet rs = pStat.getGeneratedKeys();
			if( rs.first() )
			{
				depId = rs.getInt(1);
				d.setId(depId);
				System.out.println("ID katedre je " + depId );
			}
			else
			{
				throw new SQLException("Kreiranje katedre nije izvrseno, kljuc nije generisan");
			}
			rs.close();
			
			// sad procesuiramo naucne oblasti, pa kurseve...
			java.util.Vector< ScienceDomain > sDomains = d.getScienceDomains();
			if( sDomains.size() == 0 )
			{
				return; // Nema naucnih oblasti
			}
			for( ScienceDomain sd : sDomains )
			{
				int sdomainId = 0;
				String insertSDomQuery = "INSERT INTO ScienceDomain( ScienceDomainTitle ) VALUES ( '" + sd.getTitle() + "' );" ;
				java.sql.PreparedStatement sD_stat = DBConnection.getConnection().prepareStatement(insertSDomQuery, java.sql.Statement.RETURN_GENERATED_KEYS);
				int insertedSDom = sD_stat.executeUpdate();
				if( insertedSDom == 0 )
				{
					throw new SQLException("Kreiranje naucne oblasti " + sd.getTitle() + " nije izvrseno");
				}
				java.sql.ResultSet rs1 = sD_stat.getGeneratedKeys();
				if( rs1.first() )
				{
					
					sdomainId = rs1.getInt(1);
					sd.setId(sdomainId);
				}
				else
				{
					throw new SQLException("Kreiranje naucne oblasti " + sd.getTitle() + " nije izvrseno");
				}
				
				// sad unosimo predmete
				java.util.Vector<Course> courses = sd.getCourses();
				if( courses.size() == 0 )
				{
					// nema kurseva samo upisujemo u tabelu zavisnosti id katedre i naucne oblasti i -1 tamo gde su kursevi
					String s = "INSERT INTO ScienceDomainCourses ( departmentID, scienceDomainID, courseID ) " +
							" VALUES " +
							" ( " + depId + "," + sdomainId + ", 0 ); ";
					int rows = DBConnection.getConnection().prepareStatement(s).executeUpdate();
					
					if( rows == 0 ) 
					{
						throw new SQLException("Kreiranje asocijacija nije izvrseno");
					}
					else
					{
						System.out.println("Uspesno!");
					}
					
				}
				else
				{
					
				}
				
			}
		}
		catch( SQLException e )
		{
			e.printStackTrace();
		}
		
	}
	//====================================================//
	public static void save( Department d, boolean recurse )
	{
		if( ! recurse )
		{
			return; // TODO: for now, just do deep storing
		}
		
		if( d.getId() == Common.DB_NEW_ENTRY )
		{
			
		}
	}
	//====================================================//
	public static void save( ScienceDomain sd, boolean recurse )
	{
		
	}
	//====================================================//
	public static void save( Course c, boolean recurse )
	{
		
	}
	//====================================================//


	
}
